﻿Public Class UsageSummaryReportCDRDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim tarih As String = IIf(Request.QueryString("tarih") Is Nothing, "", Request.QueryString("tarih")).ToString().Trim()
        Dim tarihArray As String() = tarih.Split("-")
        Dim basTarih As String = tarihArray(0)
        Dim bitTarih As String = IIf(tarihArray(1) = "...", DateTime.Now.ToString("dd.MM.yyyy"), tarihArray(1))
        Dim msisdn As String = IIf(Request.QueryString("msisdn") Is Nothing, "", Request.QueryString("msisdn")).ToString().Trim()
        Dim zpdes As String = IIf(Request.QueryString("zpdes") Is Nothing, "", Request.QueryString("zpdes")).ToString().Trim()

        Dim query As String = "" & _
        vbNewLine & "	SELECT      	" & _
        vbNewLine & "   CASE WHEN substr(fn_coid_to_msisdn(cust_info_contract_id),0,6)=''" & _
        vbNewLine & "THEN" & _
        vbNewLine & " ''" & _
        vbNewLine & "ELSE" & _
        vbNewLine & "substr(fn_coid_to_msisdn(cust_info_contract_id),0,6)||'XXXX'" & _
        vbNewLine & "END ""Arayan Numara""," & _
        vbNewLine & "CASE WHEN substr(eu_frm.DECRYPT_BNUMBER_NORM(o_p_number_address,o_p_normed_num_address,tariff_info_sncode,tariff_info_tmcode),0,6) is null" & _
        vbNewLine & "THEN" & _
        vbNewLine & "''" & _
        vbNewLine & "ELSE" & _
        vbNewLine & "substr(eu_frm.DECRYPT_BNUMBER_NORM(o_p_number_address,o_p_normed_num_address,tariff_info_sncode,tariff_info_tmcode),0,6)||'XXXX'" & _
        vbNewLine & "END ""Aranan Numara""," & _
        vbNewLine & "   CASE WHEN substr(eu_frm.DECRYPT_BNUMBER(o_p_number_address,o_p_normed_num_address,tariff_info_sncode,tariff_info_tmcode),0,6) is null" & _
        vbNewLine & "   THEN" & _
        vbNewLine & "   ''" & _
        vbNewLine & "   ELSE" & _
        vbNewLine & "   substr(eu_frm.DECRYPT_BNUMBER(o_p_number_address,o_p_normed_num_address,tariff_info_sncode,tariff_info_tmcode),0,6)||'XXXX'" & _
        vbNewLine & "   END ""Aranan Dialed"", " & _
        vbNewLine & "	zp.des ""Aranan Bölge Tanim"",     	" & _
        vbNewLine & "	TO_char(START_TIME_TIMESTAMP + NVL((START_TIME_OFFSET * 1/86400),0),'dd.mm.yyyy hh24:mi:ss') ""Tarih/Saat"", 	" & _
        vbNewLine & "	decode(s_pdp_address , '3A3A3A3A',sn.des||' - 3G',sn.des) "" Kullanım Türü"",     	" & _
        vbNewLine & "	DECODE(tariff_info_sncode,63,data_volume,duration_volume) "" Süre/BYTE"",     	" & _
        vbNewLine & "	rounded_volume ""Ücr Per Süre"",    	" & _
        vbNewLine & "	DECODE (tariff_info_sncode,246,rated_flat_amount,     	" & _
        vbNewLine & "	Decode(XFILE_CHARGE_AMOUNT,NULL,DECODE(FREE_CHARGE_AMOUNT,NULL,RATED_FLAT_AMOUNT,(RATED_FLAT_AMOUNT-FREE_CHARGE_AMOUNT)),     	" & _
        vbNewLine & "	(XFILE_CHARGE_AMOUNT+DECODE(FREE_CHARGE_AMOUNT,NULL,RATED_FLAT_AMOUNT,(RATED_FLAT_AMOUNT-FREE_CHARGE_AMOUNT)))))+ nvl(xfile_charge_tax,0) ""Ücretlenen Miktar"", 	" & _
        vbNewLine & "	CASE WHEN ( TARIFF_DETAIL_RTX_CHARGE_TYPE='I' AND SUBSTR(ZN.SHDES,1,1)='N' AND XFILE_IND='H' ) 	" & _
        vbNewLine & "	 THEN 'Yurt içi arama' 	" & _
        vbNewLine & "	 WHEN ( TARIFF_DETAIL_RTX_CHARGE_TYPE='I' AND SUBSTR(ZN.SHDES,1,1)='I' AND XFILE_IND='H' )  	" & _
        vbNewLine & "	 THEN 'Uluslararasi arama' 	" & _
        vbNewLine & "	 WHEN ( TARIFF_DETAIL_RTX_CHARGE_TYPE IN ('S','E','R','I') AND FOLLOW_UP_CALL_TYPE IN ( '2','5') AND XFILE_IND='V' ) 	" & _
        vbNewLine & "	 THEN 'Ulus.dol.alinan çagrilar' 	" & _
        vbNewLine & "	 WHEN ( TARIFF_DETAIL_RTX_CHARGE_TYPE IN ('S','E','R','I') AND FOLLOW_UP_CALL_TYPE = '4' AND XFILE_IND='V' AND  	" & _
        vbNewLine & "	R.BUSINESS_INFO_BS_ID='30' ) 	" & _
        vbNewLine & "	 THEN 'Ulus.dol.yapilan çagrilar' 	" & _
        vbNewLine & "	 WHEN ( TARIFF_DETAIL_RTX_CHARGE_TYPE IN ('S','E','R','I') AND FOLLOW_UP_CALL_TYPE = '1' AND  XFILE_IND='V'  ) 	" & _
        vbNewLine & "	 THEN 'Ulus.dol.yapilan çagrilar' 	" & _
        vbNewLine & "	ELSE 'Diğer' 	" & _
        vbNewLine & "	END  Aciklama, 	" & _
        vbNewLine & "	TARIFF_INFO_TMCODE "" Arayan No TMCode"",    	" & _
        vbNewLine & "	eu_frm.fnn_dnnum_to_tmcode(SUBSTR(o_p_normed_num_address,3,10),start_time_timestamp+ NVL((start_time_OFFSET * 1/86400),0)) "" Aranan No TMCode"",    	" & _
        vbNewLine & "	--fn_msisdn_to_tmcode(SUBSTR(o_p_normed_num_address,3,10)) fx,    	" & _
        vbNewLine & "	zn.des "" Zone Adi"",    	" & _
        vbNewLine & "	ISMSC_ISN_SERIAL_NUMBER "" SMS Seri NO"",    	" & _
        vbNewLine & "	r.S_P_EQUIPMENT_NUMBER ""IMEI"",    	" & _
        vbNewLine & "	decode(tariff_info_sncode,246,0,nvl(xfile_charge_amount,0)) Yurtdisi_ucret,    	" & _
        vbNewLine & "	nvl(xfile_charge_tax,0) Yurtdisi_vergi,    	" & _
        vbNewLine & "	decode(tariff_info_tmcode,7,rated_flat_amount,1371,rated_flat_amount,1372,rated_flat_amount,1373,rated_flat_amount,0) Komisyon,    	" & _
        vbNewLine & "	mpd.country country,    	" & _
        vbNewLine & "	mpd.PLMNNAME ""operator ismi"",         	" & _
        vbNewLine & "	CALLED_PARTY_MNP_ADDRESS "" MNP No"",    	" & _
        vbNewLine & "	decode(substr(nvl(CALLED_PARTY_MNP_ADDRESS,0),1,6) ,'90B001','Avea','90B002','Türkcell','90B003','Vodafone',CALLED_PARTY_MNP_ADDRESS ) ""Mnp Adres"",        	" & _
        vbNewLine & "	Decode(nvl(substr(o_p_number_address,-3,3),0) 	" & _
        vbNewLine & "	    , '.01','Sinyalleşme - ücretsiz' 	" & _
        vbNewLine & "	    , '.02','standart WAP APN trafik' 	" & _
        vbNewLine & "	    , '.03','mobilvizyon sayfaları ve trafiği' 	" & _
        vbNewLine & "	    , '.04','standart INTERNET APN trafik' 	" & _
        vbNewLine & "	    , '.05','wap.patlıcan.com.tr' 	" & _
        vbNewLine & "	    , '.06','tüm ücretsiz linkler' 	" & _
        vbNewLine & "	    , '.07','wap.avea.com.tr' 	" & _
        vbNewLine & "	    , '.08','wap.bialem.com.tr',null) ""APN TANIM"" 	" & _
        vbNewLine & "	FROM  EU_BILL.UDR_LT_ARCHIVE_DETAIL r,mpusntab sn,mpuzptab zp,mpuzntab zn ,MPDPLTAB mpd    	" & _
        vbNewLine & "	where cust_info_customer_id =FN_MSISDN_TO_CUSTID('" + msisdn + "')  " & _
        vbNewLine & "	and   mpd.plcode= NET_ELEMENT_NETWORK_CODE    	" & _
        vbNewLine & "	AND   zp.zpcode = TARIFF_INFO_ZpCODE     	" & _
        vbNewLine & "	AND   zn.zncode = TARIFF_INFO_ZnCODE     	" & _
        vbNewLine & "	AND   sn.sncode = TARIFF_INFO_SNCODE           	" & _
        vbNewLine & "	AND   UDS_CHARGE_PART_ID = DECODE(TARIFF_INFO_TMCODE,7,1,1371,1,1372,1,1373,1,UDS_CHARGE_PART_ID)     	" & _
        vbNewLine & "	AND (  entry_date_timestamp + NVL ((entry_date_offset * 1 / 86400), 0)) >=to_date('" & basTarih & "','dd.mm.yyyy') 	" & _
        vbNewLine & "	AND (  entry_date_timestamp+ NVL ((entry_date_offset * 1 / 86400), 0) ) < to_date('" & bitTarih & "','dd.mm.yyyy') 	" & _
        vbNewLine & "	and cust_info_contract_id = FN_MSISDN_TO_COID('" & msisdn & "') " & _
        vbNewLine & "	and replace(lower(zp.des),' ','')='" & zpdes & "'" & _
        vbNewLine & "   ORDER BY start_time_timestamp"
        Try
            dg.DataSource = persistent.myDBConn.myORATable_LOG(query, "", "", "", "", "select(UsageSummaryCDR)", "UsageSummaryReportCDR", Session("user").ToString())
            dg.DataBind()
        Catch ex As Exception
            Response.Write("-")
        End Try

    End Sub

End Class